------------------------------------------------------------------------------------------------------------------
USE ReportesGerenciales;
GO

------------------------------------------------------------------------------------------------------------------
DROP VIEW Empleados_CP_Individual;
GO

CREATE VIEW Empleados_CP_Individual AS
SELECT
	e.codigo_empleado,
	e.identidad,
	e.nombres,
	e.apellidos,
	c.id_cargo,
	c.descripcion [cargo],
	d.id_departamento,
	d.nombre [departamento],
	e.sueldo,
	e.fecha_contrato,
	e.acuerdo,
	e.fecha_nacimiento,
	e.impuesto_estatal,
	e.tipo_planilla,
	e.estado,
	e.sexo,
	ce.codigo_presupuestario [cuenta_presupuestaria]

FROM Empleado [e]
INNER JOIN Cargo [c] ON (c.id_cargo=e.id_cargo)
INNER JOIN Departamento [d] ON (d.id_departamento=e.codigo_departamento)
INNER JOIN Cuentas_Empleado [ce] ON (ce.codigo_empleado=e.codigo_empleado);
GO

------------------------------------------------------------------------------------------------------------------
DROP VIEW Ingresos_X_Empleado;
GO

CREATE VIEW Ingresos_X_Empleado AS
SELECT
	e.codigo_empleado,
	e.identidad,
	e.nombres,
	e.apellidos,
	c.id_cargo,
	c.descripcion [cargo],
	d.id_departamento,
	d.nombre [departamento],
	x.referencia,
	x.banco,
	x.cheque,
	x.planilla,
	x.codigo_ingreso,
	x.cantidad,
	x.tasa

FROM Empleado [e]
INNER JOIN Cargo [c] ON (c.id_cargo=e.id_cargo)
INNER JOIN Departamento [d] ON (d.id_departamento=e.codigo_departamento)
INNER JOIN IngresoXEmpleado [x] ON (x.codigo_empleado=e.codigo_empleado)
GO

------------------------------------------------------------------------------------------------------------------
DROP VIEW Deducciones_X_Empleado;
GO

CREATE VIEW Deducciones_X_Empleado AS
SELECT
	e.codigo_empleado,
	e.identidad,
	e.nombres,
	e.apellidos,
	c.id_cargo,
	c.descripcion [cargo],
	d.id_departamento,
	d.nombre [departamento],
	x.planilla,
	x.codigo_ingreso_deduccion,
	x.valor_deducciones

FROM Empleado [e]
INNER JOIN Cargo [c] ON (c.id_cargo=e.id_cargo)
INNER JOIN Departamento [d] ON (d.id_departamento=e.codigo_departamento)
INNER JOIN DeduccionXEmpleado [x] ON (x.codigo_empleado=e.codigo_empleado)
GO

------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE ListarDepartamentos;
GO

CREATE PROCEDURE ListarDepartamentos
AS
BEGIN
	SELECT id_departamento [id], nombre [value] FROM Departamento
END
GO

------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE ListarCargos;
GO

CREATE PROCEDURE ListarCargos
AS
BEGIN
	SELECT id_cargo [id], descripcion [value] FROM Cargo
END;
GO

------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE Reporte_ECPI;
GO

CREATE PROCEDURE Reporte_ECPI (@inicio as integer, @total as integer, @cargo varchar(10), @depto varchar(10), @cpres varchar(30), @estado varchar(2), @min_fecha_contrato datetime, @max_fecha_contrato datetime, @min_fecha_acuerdo datetime, @max_fecha_acuerdo datetime)
AS
BEGIN
	DECLARE @sql varchar(1000);
	DECLARE @where varchar(1000);

	SET @where =
		(CASE WHEN (@cargo IS NULL) THEN '' ELSE ' AND id_cargo=' + dbo.EscapeString(@cargo) END) +
		(CASE WHEN (@depto IS NULL) THEN '' ELSE ' AND id_departamento=' + dbo.EscapeString(@depto) END) +
		(CASE WHEN (@cpres IS NULL) THEN '' ELSE ' AND cuenta_presupuestaria=' + dbo.EscapeString(@cpres) END) +
		(CASE WHEN (@estado IS NULL) THEN '' ELSE ' AND estado=' + dbo.EscapeString(@estado) END) +
		(CASE WHEN (@min_fecha_contrato IS NULL) THEN '' ELSE ' AND fecha_contrato >= CONVERT(DATETIME,''' + CONVERT(VARCHAR(10), @min_fecha_contrato, 120) + ''')' END) +
		(CASE WHEN (@max_fecha_contrato IS NULL) THEN '' ELSE ' AND fecha_contrato <= CONVERT(DATETIME,''' + CONVERT(VARCHAR(10), @max_fecha_contrato, 120) + ''')' END) +
		(CASE WHEN (@min_fecha_acuerdo IS NULL) THEN '' ELSE ' AND acuerdo >= CONVERT(DATETIME,''' + CONVERT(VARCHAR(10), @min_fecha_acuerdo, 120) + ''')' END) +
		(CASE WHEN (@max_fecha_acuerdo IS NULL) THEN '' ELSE ' AND acuerdo <= CONVERT(DATETIME,''' + CONVERT(VARCHAR(10), @max_fecha_acuerdo, 120) + ''')' END)
	;

	SET @sql = 
	'SELECT TOP ('+CONVERT(varchar, @total)+') * '+
	'FROM Empleados_CP_Individual '+
	'WHERE codigo_empleado NOT IN ('+
		'SELECT TOP ('+CONVERT(varchar, @inicio)+') codigo_empleado FROM Empleados_CP_Individual '+
		'WHERE 0=0' + @where +
	')' + @where;

	EXEC (@sql);
END;
GO

------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE Contar_Reporte_ECPI;
GO

CREATE PROCEDURE Contar_Reporte_ECPI (@cargo varchar(10), @depto varchar(10), @cpres varchar(30), @estado varchar(2), @min_fecha_contrato datetime, @max_fecha_contrato datetime, @min_fecha_acuerdo datetime, @max_fecha_acuerdo datetime)
AS
BEGIN
	DECLARE @where varchar(1000);

	SET @where =
		(CASE WHEN (@cargo IS NULL) THEN '' ELSE ' AND id_cargo=' + dbo.EscapeString(@cargo) END) +
		(CASE WHEN (@depto IS NULL) THEN '' ELSE ' AND id_departamento=' + dbo.EscapeString(@depto) END) +
		(CASE WHEN (@cpres IS NULL) THEN '' ELSE ' AND cuenta_presupuestaria=' + dbo.EscapeString(@cpres) END) +
		(CASE WHEN (@estado IS NULL) THEN '' ELSE ' AND estado=' + dbo.EscapeString(@estado) END) +
		(CASE WHEN (@min_fecha_contrato IS NULL) THEN '' ELSE ' AND fecha_contrato >= CONVERT(DATETIME,''' + CONVERT(VARCHAR(10), @min_fecha_contrato, 120) + ''')' END) +
		(CASE WHEN (@max_fecha_contrato IS NULL) THEN '' ELSE ' AND fecha_contrato <= CONVERT(DATETIME,''' + CONVERT(VARCHAR(10), @max_fecha_contrato, 120) + ''')' END) +
		(CASE WHEN (@min_fecha_acuerdo IS NULL) THEN '' ELSE ' AND acuerdo >= CONVERT(DATETIME,''' + CONVERT(VARCHAR(10), @min_fecha_acuerdo, 120) + ''')' END) +
		(CASE WHEN (@max_fecha_acuerdo IS NULL) THEN '' ELSE ' AND acuerdo <= CONVERT(DATETIME,''' + CONVERT(VARCHAR(10), @max_fecha_acuerdo, 120) + ''')' END)
	;

	EXEC ('SELECT COUNT(*) num FROM Empleados_CP_Individual WHERE 0=0' + @where);
END;
GO

------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE Reporte_IXE;
GO

CREATE PROCEDURE Reporte_IXE (@inicio as integer, @total as integer)
AS
BEGIN
	DECLARE @sql varchar(1000);
	DECLARE @where varchar(1000);

	SET @where = '';

	SET @sql = 
	'SELECT TOP ('+CONVERT(varchar, @total)+') * '+
	'FROM Ingresos_X_Empleado '+
	'WHERE codigo_empleado NOT IN ('+
		'SELECT TOP ('+CONVERT(varchar, @inicio)+') codigo_empleado FROM Ingresos_X_Empleado '+
		'WHERE 0=0' + @where +
	')' + @where;

	EXEC (@sql);
END;
GO

------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE Contar_Reporte_IXE;
GO

CREATE PROCEDURE Contar_Reporte_IXE
AS
BEGIN
	SELECT COUNT(*) num FROM Ingresos_X_Empleado;
END;
GO

------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE Reporte_EXE;
GO

CREATE PROCEDURE Reporte_EXE (@inicio as integer, @total as integer)
AS
BEGIN
	DECLARE @sql varchar(1000);
	DECLARE @where varchar(1000);

	SET @where = '';

	SET @sql = 
	'SELECT TOP ('+CONVERT(varchar, @total)+') * '+
	'FROM Deducciones_X_Empleado '+
	'WHERE codigo_empleado NOT IN ('+
		'SELECT TOP ('+CONVERT(varchar, @inicio)+') codigo_empleado FROM Deducciones_X_Empleado '+
		'WHERE 0=0' + @where +
	')' + @where;

	EXEC (@sql);
END;
GO

------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE Contar_Reporte_EXE;
GO

CREATE PROCEDURE Contar_Reporte_EXE
AS
BEGIN
	SELECT COUNT(*) num FROM Deducciones_X_Empleado;
END;
GO

------------------------------------------------------------------------------------------------------------------
-- REHABILITAR 
-- GRANT EXECUTE ON ListarDepartamentos TO master
-- GRANT EXECUTE ON ListarCargos TO master
-- GRANT EXECUTE ON Reporte_ECPI TO master
-- GRANT EXECUTE ON Contar_Reporte_ECPI TO master
-- GRANT EXECUTE ON Reporte_IXE TO master
-- GRANT EXECUTE ON Contar_Reporte_IXE TO master

-- GRANT EXECUTE ON ListarDepartamentos TO usuario
-- GRANT EXECUTE ON ListarCargos TO usuario
-- GRANT EXECUTE ON Reporte_ECPI TO usuario
-- GRANT EXECUTE ON Contar_Reporte_ECPI TO usuario
-- GRANT EXECUTE ON Reporte_IXE TO usuario
-- GRANT EXECUTE ON Contar_Reporte_IXE TO usuario

------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE ReportePresupuestoXPeriodo;
GO

CREATE PROCEDURE ReportePresupuestoXPeriodo(@programa varchar(3))
AS
BEGIN
	DECLARE @sql varchar(1000);
	DECLARE @where varchar(1000);

	SET @where=(CASE WHEN @programa IS NULL THEN ''
	ELSE 'WHERE SUBSTRING(codigo,5,3)='+dbo.EscapeString(@programa) END);

	SET @sql='SELECT SUBSTRING(codigo,5,3) as programa,presupuesto,(cantidad_modificada-cantidad) as transferencia,'+
	'cantidad_modificada,cantidad_usada,ROUND(cantidad_modificada-cantidad_usada,4) as disponible,'+
	'ROUND(100*cantidad_usada/cantidad_modificada,4) as porc_utilizado,'+
	'ROUND(100*((cantidad_modificada-cantidad_usada)/cantidad_modificada),4) as porc_disponible FROM PresupuestoXPeriodo '+@where;
	EXEC(@sql)
END;
GO

------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE ReporteGrupo;
GO

CREATE PROCEDURE ReporteGrupo(@grupo varchar(1))
AS
BEGIN
	DECLARE @sql varchar(1000);
	DECLARE @where varchar(1000);
	
	SET @where=(CASE WHEN @grupo IS NULL THEN ''
	ELSE 'WHERE SUBSTRING(codigo,17,1)='+dbo.EscapeString(@grupo) END);
	
	SET @sql='SELECT SUBSTRING(codigo,17,1) as grupo,presupuesto,(cantidad_modificada-cantidad) as transferencia,'+
	'cantidad_modificada,cantidad_usada,ROUND(cantidad_modificada-cantidad_usada,4) as disponible,'+
	'ROUND(100*cantidad_usada/cantidad_modificada,4) as porc_utilizado,'+
	'ROUND(100*((cantidad_modificada-cantidad_usada)/cantidad_modificada),4) as porc_disponible FROM PresupuestoXPeriodo '+@where;
	EXEC(@sql)
END;
GO

------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE ReporteSubGrupo;
GO

CREATE PROCEDURE ReporteSubGrupo(@subgrupo varchar(1))
AS
BEGIN
	DECLARE @sql varchar(1000);
	DECLARE @where varchar(1000);
	
	SET @where=(CASE WHEN @subgrupo IS NULL THEN ''
	ELSE 'WHERE SUBSTRING(codigo,18,1)='+dbo.EscapeString(@subgrupo) END);
	
	SET @sql='SELECT SUBSTRING(codigo,18,1) as grupo,presupuesto,(cantidad_modificada-cantidad) as transferencia,'+
	'cantidad_modificada,cantidad_usada,ROUND(cantidad_modificada-cantidad_usada,4) as disponible,'+
	'ROUND(100*cantidad_usada/cantidad_modificada,4) as porc_utilizado,'+
	'ROUND(100*((cantidad_modificada-cantidad_usada)/cantidad_modificada),4) as porc_disponible FROM PresupuestoXPeriodo '+@where;
	EXEC(@sql)
END;
GO

------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE Contar_ReportePresupuestoXPeriodo;
GO

CREATE PROCEDURE Contar_ReportePresupuestoXPeriodo(@programa varchar(3))
AS
BEGIN
	DECLARE @where varchar(1000);
	SET @where=
	(CASE WHEN @programa IS NULL THEN '' ELSE 'WHERE SUBSTRING(codigo,5,3)='+dbo.EscapeString(@programa) END);
	EXEC('SELECT COUNT(*) as num FROM PresupuestoXPeriodo '+@where);
END;
GO

------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE Contar_ReporteGrupo;
GO

CREATE PROCEDURE Contar_ReporteGrupo(@grupo varchar(1))
AS
BEGIN
	DECLARE @where varchar(1000);
	
	SET @where=(CASE WHEN @grupo IS NULL THEN ''
	ELSE 'WHERE SUBSTRING(codigo,17,1)='+dbo.EscapeString(@grupo) END);
	
	EXEC('SELECT COUNT(*) as num FROM PresupuestoXPeriodo '+@where);
END;
GO

------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE Contar_ReporteSubGrupo;
GO

CREATE PROCEDURE Contar_ReporteSubGrupo(@subgrupo varchar(1))
AS
BEGIN
	DECLARE @where varchar(1000);
	
	SET @where=(CASE WHEN @subgrupo IS NULL THEN ''
	ELSE 'WHERE SUBSTRING(codigo,18,1)='+dbo.EscapeString(@subgrupo) END);
	
	EXEC('SELECT COUNT(*) as num FROM PresupuestoXPeriodo '+@where);
END;
GO

------------------------------------------------------------------------------------------------------------------
-- REHABILITAR 
-- GRANT EXECUTE ON ReportePresupuestoXPeriodo TO master
-- GRANT EXECUTE ON ReporteGrupo TO master
-- GRANT EXECUTE ON ReporteSubGrupo TO master
-- GRANT EXECUTE ON Contar_ReportePresupuestoXPeriodo TO master
-- GRANT EXECUTE ON Contar_ReporteGrupo TO master
-- GRANT EXECUTE ON Contar_ReporteSubGrupo TO master

-- GRANT EXECUTE ON ReportePresupuestoXPeriodo TO usuario
-- GRANT EXECUTE ON ReporteGrupo TO usuario
-- GRANT EXECUTE ON ReporteSubGrupo TO usuario
-- GRANT EXECUTE ON Contar_ReportePresupuestoXPeriodo TO usuario
-- GRANT EXECUTE ON Contar_ReporteGrupo TO usuario
-- GRANT EXECUTE ON Contar_ReporteSubGrupo TO usuario
